﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace iHub
{
    public class EmployeeManager
    {
        private dbConnector db = new dbConnector();
        private SqlDataReader read;
        private SqlCommand command;

        public void InsertEmployee(Employee newEmployee)
        {
            db.connect();

            command = new SqlCommand("dbo.AddEmployee", db.getCon());
            command.CommandType = CommandType.StoredProcedure;

            //insert paramaters / values

            command.Parameters.AddWithValue("@tin", newEmployee.TIN);
            command.Parameters.AddWithValue("@name", newEmployee.Name);
            command.Parameters.AddWithValue("@address", newEmployee.Address);
            command.Parameters.AddWithValue("@city", newEmployee.City);
            command.Parameters.AddWithValue("@contact", newEmployee.ContactNo);
            command.Parameters.AddWithValue("@status", newEmployee.Status);

            command.ExecuteNonQuery();

            db.disconnect();
        }

        public void EditEmployee(Employee newEmployee)
        {
            db.connect();

            command = new SqlCommand("dbo.EditEmployee", db.getCon());
            command.CommandType = CommandType.StoredProcedure;

            //insert paramaters / values
            command.Parameters.AddWithValue("@idno", newEmployee.IdNo);
            command.Parameters.AddWithValue("@tin", newEmployee.TIN);
            command.Parameters.AddWithValue("@name", newEmployee.Name);
            command.Parameters.AddWithValue("@address", newEmployee.Address);
            command.Parameters.AddWithValue("@city", newEmployee.City);
            command.Parameters.AddWithValue("@contact", newEmployee.ContactNo);

            command.ExecuteNonQuery();

            db.disconnect();
        }

        public void TerminateEmployee(Employee newEmployee)
        {
            db.connect();

            command = new SqlCommand("dbo.TerminateEmployee", db.getCon());
            command.CommandType = CommandType.StoredProcedure;

            //insert paramaters / values
            command.Parameters.AddWithValue("@idno", newEmployee.IdNo);
            command.Parameters.AddWithValue("@name", newEmployee.Name);
            command.Parameters.AddWithValue("@status", newEmployee.Status);

            command.ExecuteNonQuery();

            db.disconnect();
        }

        public void HireEmployee(Employee newEmployee)
        {
            db.connect();

            command = new SqlCommand("dbo.HireEmployee", db.getCon());
            command.CommandType = CommandType.StoredProcedure;

            //insert paramaters / values
            command.Parameters.AddWithValue("@idno", newEmployee.IdNo);
            command.Parameters.AddWithValue("@name", newEmployee.Name);
            command.Parameters.AddWithValue("@status", newEmployee.Status);

            command.ExecuteNonQuery();

            db.disconnect();
        }

        public static void EditList(String a, int b)
        {


        }

        public List<string> ViewEmployee()
        {
            db.connect();

            command = new SqlCommand("select * from Employee ORDER by e_name ASC", db.getCon());
           
            read = command.ExecuteReader();
            String name1, dash = "-";
            String item;

            //  list = new DataSet();
            int id1;

            //    DataRow list = new DataRow();
            List<string> _items = new List<string>();
            while (read.Read())
            {
                name1 = (string)read.GetValue(2);
                name1 = name1 += dash;
                id1 = (int)read.GetValue(0);
                item = name1 += id1;

                //Convert.ToString(id1);

                _items.Add(item);
            }

            read.Close();
            db.disconnect();

            return _items;
        }

        public String EmployeeTIN(String i)
        {
            String id2 = i, value;

            db.connect();
            command = new SqlCommand("select * from Employee where e_id = '" + id2 + "'", db.getCon());
            // string conn = "Data Source=GABS-PC\\SQLEXPRESS;Initial Catalog=DB_IHUB;Integrated Security=True";

            read = command.ExecuteReader();
            read.Read();

            value = (string)read.GetValue(1);
            read.Close();
            db.disconnect();

            return value;
        }

        public String EmployeeAddress(String i)
        {
            String id2 = i, value;
            //string conn = "Data Source=GABS-PC\\SQLEXPRESS;Initial Catalog=DB_IHUB;Integrated Security=True";

            db.connect();

            command  = new SqlCommand("select * from Employee where e_id = '" + id2 + "'", db.getCon());
            read = command.ExecuteReader();
            read.Read();


            value = (string)read.GetValue(3);

            read.Close();
            db.disconnect();

            return value;
        }

        public String EmployeeCity(String i)
        {
            String id2 = i, value;
            // string conn = "Data Source=GABS-PC\\SQLEXPRESS;Initial Catalog=DB_IHUB;Integrated Security=True";

            db.connect();

            command = new SqlCommand("select * from Employee where e_id = '" + id2 + "'", db.getCon());
            read = command.ExecuteReader();
            read.Read();

            value = (string)read.GetValue(4);
            read.Close();
            db.disconnect();
            return value;
        }

        public int EmployeeContact(String i)
        {
            String id2 = i;
            int value;

            db.connect();
            //    string conn = "Data Source=GABS-PC\\SQLEXPRESS;Initial Catalog=DB_IHUB;Integrated Security=True";

            command = new SqlCommand("select * from Employee where e_id = '" + id2 + "'", db.getCon());
            read = command.ExecuteReader();
            read.Read();

            value = Convert.ToInt32(read.GetValue(5));

            read.Close();
            db.disconnect();

            return value;
        }

        public Boolean EmployeeBoolean(String i)
        {
            String id2 = i;
            Boolean value;

            db.connect();
            //string conn = "Data Source=GABS-PC\\SQLEXPRESS;Initial Catalog=DB_IHUB;Integrated Security=True";

            command = new SqlCommand("select * from Employee where e_id = '" + id2 + "'", db.getCon());
            read = command.ExecuteReader();
            read.Read();

            value = (Boolean)read.GetValue(6);

            read.Close();
            db.disconnect();

            return value;
        }

        public List<string> ViewAssignments(string i)
        {
            String id2 = i;
            String request;
            String assign;
            String status;
            String dash;
            String item1;

            db.connect();
            // string conn = "Data Source=GABS-PC\\SQLEXPRESS;Initial Catalog=DB_IHUB;Integrated Security=True";

            command = new SqlCommand("select * from Assignment where e_id = '" + id2 + "'", db.getCon());
            read = command.ExecuteReader();
            List<string> _items = new List<string>();
            while (read.Read())
            {
                request = (string)read.GetValue(3);
                assign = (string)read.GetValue(4);
                status = (string)read.GetValue(6);
                dash = "-";
                item1 = request += dash += assign += dash += status;
                _items.Add(item1);
            }

            read.Close();
            db.disconnect();

            return _items;
        }

        public List<string> ViewAccAssignments(string i)
        {
            String id2 = i;
            List<string> _items = new List<string>();
            String request;
            String assign;
            String status;
            String dash;
            String item1;
            int id;
            String id3;
            DataTable accassigns = new DataTable();
            DataRow row;
            int no;
            int x;
            SqlDataReader r2;
            SqlCommand com2;

            x = 0;
            db.connect();
            // string conn = "Data Source=GABS-PC\\SQLEXPRESS;Initial Catalog=DB_IHUB;Integrated Security=True";
            accassigns.Columns.Add(new DataColumn("AssignNo", typeof(int)));
            accassigns.Columns.Add(new DataColumn("Name", typeof(string)));

            command = new SqlCommand("select * from Assignment where e_id = '" + id2 + "' and status = 'Complete'", db.getCon());
            read = command.ExecuteReader();

            while (read.Read())
            {
                id = (int)read.GetValue(0);
                row = accassigns.NewRow();
                id3 = id.ToString();
                request = (string)read.GetValue(4);
                assign = (string)read.GetValue(9);
                status = (string)read.GetValue(10);
                dash = "-";
                item1 = id3 += dash += request += dash += assign += dash += status;
                row["AssignNo"] = id;
                row["Name"] = item1;
                accassigns.Rows.Add(row);
            }

            read.Close();

            com2 = new SqlCommand("select * from Payroll_Assignment",db.getCon());
            r2 = com2.ExecuteReader();

            try
            {
                while (r2.Read())
                {
                    x = 0;
                    no = Convert.ToInt32(r2["assign_id"]);

                    while (x < accassigns.Rows.Count)
                    {
                        if (no == (int)accassigns.Rows[x]["AssignNo"])
                        {
                            accassigns.Rows[x].Delete();
                        }
                        x++;
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }

            x = 0;
            foreach (DataRow drow in accassigns.Rows)
            {
                _items.Add((string)accassigns.Rows[x]["Name"]);
                x++;
            }

            r2.Close();
            db.disconnect();

            return _items;
        }
    }
}
